/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vzone.tools;

import java.util.*;
import java.sql.*;

public abstract class SqlHelper
{

    /**
     * 连接数据库
     *
     * @return
     */
    public static Connection getConnect(String server, int port, String database, String user, String pwd)
    {

	try
	{
	    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
	    String connectionString="";

	    connectionString="jdbc:sqlserver://"+server+":"+port+";database="+database+";user="+user+";password="+pwd+";";

	    return DriverManager.getConnection(connectionString);
	}
	catch (Exception e)
	{
	    return null;
	}
    }
    public static Connection getConnect()
    {
	sqlConfig config=sqlConfig.getSqlConfig();
	return getConnect(config.server, config.port, config.database, config.user, config.pwd);
    }

    /**
     * 用于执行语句（eg：insert语句，update语句，delete语句）
     *
     * @param String
     *            cmdtext,SQL语句
     * @param OracleParameter[]
     *            parms,参数集合
     * @return int,SQL语句影响的行数
     */
    public static int ExecuteNonQuery(Connection conn, String cmdtext, Object[] parms)
	    throws Exception
    {
	PreparedStatement pstmt = null;

	try
	{
	    pstmt = conn.prepareStatement(cmdtext);
	    prepareCommand(pstmt, parms);

	    return pstmt.executeUpdate();

	}
	catch (Exception e)
	{
	    throw new Exception("executeNonQuery方法出错:" + e.getMessage());
	}
	finally
	{
	    try
	    {
		if (pstmt != null)
		{
		    pstmt.close();
		}
		if (conn != null)
		{
		    conn.close();
		}
	    }
	    catch (Exception e)
	    {
		throw new Exception("执行executeNonQuery方法出错:" + e.getMessage());
	    }
	}
    }

    /**
     * 用于获取结果集语句（eg：selete * from table）
     *
     * @param cmdtext
     * @param parms
     * @return ResultSet
     * @throws Exception
     */
    public static ArrayList ExecuteReader(Connection conn, String cmdtext, Object[] parms)
	    throws Exception
    {
	PreparedStatement pstmt = null;

	try
	{
	    pstmt = conn.prepareStatement(cmdtext);

	    prepareCommand(pstmt, parms);
	    ResultSet rs = pstmt.executeQuery();

	    ArrayList al = new ArrayList();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int column = rsmd.getColumnCount();

	    while (rs.next())
	    {
		Object[] ob = new Object[column];
		for (int i = 1; i <= column; i++)
		{
		    ob[i - 1] = rs.getObject(i);
		}
		al.add(ob);
	    }

	    rs.close();
	    return al;

	}
	catch (Exception e)
	{
	    throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
	}
	finally
	{
	    try
	    {
		if (pstmt != null)
		{
		    pstmt.close();
		}
		if (conn != null)
		{
		    conn.close();
		}
	    }
	    catch (Exception e)
	    {
		throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
	    }
	}
    }

    /**
     * 用于获取单字段值语句（用名字指定字段）
     *
     * @param cmdtext
     *            SQL语句
     * @param name
     *            列名
     * @param parms
     *            OracleParameter[]
     * @return Object
     * @throws Exception
     */
    public static Object ExecuteScalar(Connection conn, String cmdtext, String name,
	    Object[] parms) throws Exception
    {
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	try
	{
	    pstmt = conn.prepareStatement(cmdtext);
	    prepareCommand(pstmt, parms);

	    rs = pstmt.executeQuery();
	    if (rs.next())
	    {
		return rs.getObject(name);
	    }
	    else
	    {
		return null;
	    }
	}
	catch (Exception e)
	{
	    throw new Exception("executeSqlObject方法出错:" + e.getMessage());
	}
	finally
	{
	    try
	    {
		if (rs != null)
		{
		    rs.close();
		}
		if (pstmt != null)
		{
		    pstmt.close();
		}
		if (conn != null)
		{
		    conn.close();
		}
	    }
	    catch (Exception e)
	    {
		throw new Exception("executeSqlObject方法出错:" + e.getMessage());
	    }
	}
    }

    /**
     * 用于获取单字段值语句（用序号指定字段）
     *
     * @param cmdtext
     *            SQL语句
     * @param index
     *            列名索引
     * @param parms
     *            OracleParameter[]
     * @return Object
     * @throws Exception
     */
    public static Object ExecuteScalar(Connection conn, String cmdtext, int index, Object[] parms)
	    throws Exception
    {
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	try
	{
	    pstmt = conn.prepareStatement(cmdtext);
	    prepareCommand(pstmt, parms);

	    rs = pstmt.executeQuery();
	    if (rs.next())
	    {
		return rs.getObject(index);
	    }
	    else
	    {
		return null;
	    }
	}
	catch (Exception e)
	{
	    throw new Exception("executeSqlObject方法出错:" + e.getMessage());
	}
	finally
	{
	    try
	    {
		if (rs != null)
		{
		    rs.close();
		}
		if (pstmt != null)
		{
		    pstmt.close();
		}
		if (conn != null)
		{
		    conn.close();
		}
	    }
	    catch (Exception e)
	    {
		throw new Exception("executeSqlObject方法出错:" + e.getMessage());
	    }
	}
    }

    /**
     * @param pstmt
     * @param cmdtext
     * @param parms
     *            Object[]
     * @throws Exception
     */
    private static void prepareCommand(PreparedStatement pstmt, Object[] parms) throws Exception
    {

	if (parms != null)
	{
	    for (int i = 0; i < parms.length; i++)
	    {
		pstmt.setObject(i+1, parms[i]);
	    }
	}
    }
}
